package com.chixing.dao.impl;

import com.chixing.dao.DBConn;
import com.chixing.dao.EmployeeDao;
import com.chixing.pojo.Employee;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/*

   java.sql.Date extends java.util.Date


   java.util.Date d1 = new  java.sql.Date();  // sql ---> util
                      new java.sql.Date(  new java.util.Date().getTime()  )     // util ---> sql



 */
public class EmployeeDaoImpl implements EmployeeDao {
    @Override
    public Employee selectByPrimaryKey(int empno) {
        Employee emp  =null;

        Connection connection = DBConn.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        String sql="select empno,ename,job,mgr,hiredate,salary,comm,deptno from emp where empno = ?";
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1,empno);
            rs = pstmt.executeQuery();

            while(rs.next()){
                empno = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString(3);
                int mgr = rs.getInt(4);
                 java.sql.Date hiredate = rs.getDate(5);  // java.sql.Date , java.util.Date
                // hiredate
                float salary = rs.getFloat(6);
                float comm = rs.getFloat(7);
                int deptno = rs.getInt(8);

                emp = new Employee(empno,ename,job,mgr,salary,comm,hiredate,deptno);

            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if(rs !=null) {
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(pstmt !=null) {
                try {
                    pstmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBConn.closeConnection();
        }

        return emp;
    }

    @Override
    public int insert(Employee employee) {
        Connection connection = DBConn.getConnection();
        String sql = "insert into emp values(?,?,?,?,?,?,?,?)";
        PreparedStatement pstmt = null;
        int rows = 0;
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1,employee.getEmpNo());
            pstmt.setString(2,employee.getEmpName());
            pstmt.setString(3,employee.getEmpJob());
            pstmt.setInt(4,employee.getEmpMgr());
            pstmt.setDate(5, new java.sql.Date(employee.getEmpHiredate().getTime())); // setDate(5, java.sql.Date)
            pstmt.setFloat(6,employee.getEmpSalary());
            pstmt.setFloat(7,employee.getEmpComm());
            pstmt.setInt(8,employee.getDeptno());


            rows =  pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if(pstmt !=null) {
                try {
                    pstmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBConn.closeConnection();
        }

        return rows;
    }

    @Override
    public int update(Employee employee) {
        return 0;
    }

    @Override
    public int delete(int empno) {
        return 0;
    }

    @Override
    public List<Employee> selectByPage(int startNum) {
        String sql ="select empno,ename,job,mgr,hiredate,salary,comm,deptno from emp limit ?,?";
        List<Employee> employeeList = new ArrayList<>( );
        Connection connection = DBConn.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1,startNum);
            pstmt.setInt(2,5);
           rs =  pstmt.executeQuery();

            while(rs.next()){
                int empno = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString(3);
                int mgr = rs.getInt(4);
                java.sql.Date hiredate = rs.getDate(5);  // java.sql.Date , java.util.Date
                // hiredate
                float salary = rs.getFloat(6);
                float comm = rs.getFloat(7);
                int deptno = rs.getInt(8);

                Employee emp = new Employee(empno,ename,job,mgr,salary,comm,hiredate,deptno);
                employeeList.add(emp);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if(rs !=null) {
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(pstmt !=null) {
                try {
                    pstmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBConn.closeConnection();
        }
        return employeeList;
    }
}
